﻿using EasyNet.Componets.Core.Excels;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EasyNet.Componets.Excels
{
    public class NPOIExcel : IExcel
    {
        private IWorkbook _workbook;
        private ISheet _currentSheet;
        private IRow _currentRow;
        private ICell _currentCell;

        #region Properties
        /// <summary>
        /// 当前操作的 SheetName
        /// </summary>
        public string CurrentSheetName
        {
            get
            {
                if (_currentSheet == null) return null;
                return _currentSheet.SheetName;
            }
        }

        /// <summary>
        /// 当前操作的 SheetIndex
        /// </summary>
        public int CurrentSheetIndex
        {
            get
            {
                if (_currentSheet == null || _workbook == null) return -1;
                return _workbook.GetSheetIndex(_currentSheet.SheetName);
            }
        }

        /// <summary>
        /// 当前操作的 RowIndex
        /// </summary>
        public int CurrentRowIndex
        {
            get
            {
                if (_currentRow == null) return -1;
                return _currentRow.RowNum;
            }
        }

        /// <summary>
        /// 当前操作的 ColumnIndex
        /// </summary>
        public int CurrentColumnIndex
        {
            get
            {
                if (_currentCell == null) return -1;
                return _currentCell.ColumnIndex;
            }
        }
        /// <summary>
        /// 激活的 SheetIndex
        /// </summary>
        public int ActiveSheetIndex
        {
            get
            {
                if (_workbook == null) return -1;
                return _workbook.ActiveSheetIndex;
            }
        }
        #endregion

        #region Workbook
        /// <summary>
        /// 打开文件
        /// </summary>
        /// <param name="file">文件名</param>
        public void Open(string file)
        {
            if(string.IsNullOrEmpty(file) || !File.Exists(file))
            {
                throw new Exception(string.Format("打开文件失败！文件不存在：{0}", file));
            }
            try
            {
                using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
                    if (file.ToLower().EndsWith(".xlsx")) // 2007版本
                        _workbook = new XSSFWorkbook(fs);
                    else if (file.ToLower().EndsWith(".xls")) // 2003版本
                        _workbook = new HSSFWorkbook(fs);
                }
            }
            catch
            {

                using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    if (file.ToLower().EndsWith(".xlsx")) // 2007版本
                        _workbook = new HSSFWorkbook(fs);
                    else if (file.ToLower().EndsWith(".xls")) // 2003版本
                        _workbook = new XSSFWorkbook(fs);
                }
            }
        }

        private void CheckNull(object obj, string msg)
        {
            if(obj == null)
            {
                throw new Exception(msg);
            }
        }
        #endregion

        #region Sheet
        /// <summary>
        /// 激活Sheet
        /// </summary>
        /// <param name="sheetIndex">Sheet索引号：起始索引号从0开始</param>
        public void ActiveSheet(int sheetIndex)
        {
            CheckNull(_workbook, "ActiveSheet失败！Workbook不能为空，请先打开文件！");
            _workbook.SetActiveSheet(sheetIndex);
        }

        /// <summary>
        /// 创建Sheet
        /// 被创建的Sheet将会成为当前操作的Sheet
        /// </summary>
        public void CreateSheet()
        {
            CheckNull(_workbook, "CreateSheet 失败！Workbook 不能为空，请先打开文件！");

            _currentSheet = _workbook.CreateSheet();

            CheckNull(_currentSheet, "CreateSheet 失败！CurrentSheet 为 Null");
        }

        /// <summary>
        /// 创建Sheet
        /// 被创建的Sheet将会成为当前Sheet
        /// </summary>
        /// <param name="sheetName"></param>
        public void CreateSheet(string sheetName)
        {
            CheckNull(_workbook, "CreateSheet 失败！Workbook 不能为空，请先打开文件！");

            _currentSheet = _workbook.CreateSheet(sheetName);

            CheckNull(_currentSheet, string.Format("CreateSheet 失败！CurrentSheet 为 Null。SheetName：{0}", sheetName));
        }

        /// <summary>
        /// 设置当前Sheet
        /// </summary>
        /// <param name="sheetName"></param>
        public void SetCurrentSheet(string sheetName)
        {
            CheckNull(_workbook, "SetCurrentSheet 失败！Workbook 不能为空，请先打开文件！");

            _currentSheet = _workbook.GetSheet(sheetName);

            //CheckNull(_currentSheet, "SetCurrentSheet 失败！CurrentSheet 为 Null");
        }

        /// <summary>
        /// 设置当前Sheet
        /// </summary>
        /// <param name="sheetIndex">Sheet索引号：起始从0开始</param>
        public void SetCurrentSheet(int sheetIndex)
        {
            CheckNull(_workbook, "SetCurrentSheet 失败！Workbook 不能为空，请先打开文件！");

            _currentSheet = _workbook.GetSheetAt(sheetIndex);

            //CheckNull(_currentSheet, "SetCurrentSheet 失败！CurrentSheet 为 Null");
        }

        /// <summary>
        /// 激活公式
        /// </summary>
        public void ActiveFormula()
        {
            CheckNull(_currentSheet, "ActiveFormula 失败！CurrentSheet 为 Null");
            _currentSheet.ForceFormulaRecalculation = true;
        }

        /// <summary>
        /// 禁用公式
        /// </summary>
        public void DisableFormula()
        {
            CheckNull(_currentSheet, "DisableFormula 失败！CurrentSheet 为 Null");
            _currentSheet.ForceFormulaRecalculation = false;
        }
        #endregion

        #region Row
        /// <summary>
        /// 设置当前Row
        /// </summary>
        /// <param name="rowIndex">行索引号：起始索引从0开始</param>
        public void SetCurrentRow(int rowIndex)
        {
            CheckNull(_currentSheet, "SetCurrentRow 失败！CurrentSheet 不能为空，请先 SetCurrentSheet！");

            _currentRow = _currentSheet.GetRow(rowIndex);

            //CheckNull(_currentRow, "SetCurrentRow 失败！CurrentRow 为 Null");
        }

        /// <summary>
        /// 创建Row
        /// 被创建的Row会被设置成新的CurrentRow
        /// </summary>
        /// <param name="rowIndex">行索引号：起始索引从0开始</param>
        public void CreateRow(int rowIndex)
        {
            CheckNull(_currentSheet, "CreateRow 失败！CurrentSheet 不能为空，请先 SetCurrentSheet！");

            _currentRow = _currentSheet.CreateRow(rowIndex);

            CheckNull(_currentRow, string.Format("CreateRow 失败！CurrentRow 为 Null。 RowIndex：{0}", rowIndex));
        }

        /// <summary>
        /// 创建Row
        /// 被创建的Row会被设置成新的CurrentRow
        /// </summary>
        /// <param name="rowIndex">行索引号：起始索引从0开始</param>
        /// <param name="sheetIndex">Sheet 索引号：起始索引从0开始</param>
        public void CreateRow(int rowIndex, int sheetIndex)
        {
            SetCurrentSheet(sheetIndex);

            CreateRow(rowIndex);
        }
        #endregion

        #region Cell
        /// <summary>
        /// 创建Cell
        /// 被创建的 Cell 会被设置成新的 CurrentCell
        /// </summary>
        /// <param name="columnIndex">列索引号：起始索引从0开始</param>
        public void CreateCell(int columnIndex)
        {
            CheckNull(_currentRow, "SetCurrentCell 失败！CurrentRow 不能为空，请先 SetCurrentRow！");

            _currentCell = _currentRow.GetCell(columnIndex);
            if (_currentCell == null)
            {
                _currentCell = _currentRow.CreateCell(columnIndex);
            }
            CheckNull(_currentCell, string.Format("SetCurrentCell 失败！CurrentCell 为 Null。 ColumnIndex：{0}", columnIndex));
        }

        /// <summary>
        /// 设置当前单元格
        /// </summary>
        /// <param name="columnIndex">列索引号：起始索引从0开始</param>
        public void SetCurrentCell(int columnIndex)
        {
            CheckNull(_currentRow, "SetCurrentCell 失败！CurrentRow 不能为空，请先 SetCurrentRow！");

            _currentCell = _currentRow.GetCell(columnIndex);

            //CheckNull(_currentCell, "SetCurrentCell 失败！CurrentCell 为 Null");

        }

        /// <summary>
        /// 读取 当前单元格值
        /// </summary>
        /// <returns>单元格值</returns>
        public object ReadCell()
        {
            object obj = null;

            if (_currentCell == null) return obj;
            switch (_currentCell.CellType)
            {
                case CellType.String:
                    obj = _currentCell.StringCellValue;
                    break;
                case CellType.Boolean:
                    obj = _currentCell.BooleanCellValue;
                    break;
                case CellType.Error:
                    obj = ErrorEval.GetText(_currentCell.ErrorCellValue);
                    break;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(_currentCell))
                    {
                        obj = DateTime.FromOADate(_currentCell.NumericCellValue);
                    }
                    else
                    {
                        obj = _currentCell.NumericCellValue;
                    }
                    break;
                case CellType.Formula:
                    switch (_currentCell.CachedFormulaResultType)
                    {
                        case CellType.Boolean:
                            obj = _currentCell.BooleanCellValue;
                            break;
                        case CellType.Error:
                            obj = ErrorEval.GetText(_currentCell.ErrorCellValue);
                            break;
                        case CellType.Numeric:
                            if (DateUtil.IsCellDateFormatted(_currentCell))
                            {
                                obj = DateTime.FromOADate(_currentCell.NumericCellValue);
                            }
                            else
                            {
                                obj = _currentCell.NumericCellValue;
                            }
                            break;
                        case CellType.String:
                            obj = _currentCell.StringCellValue;
                            break;
                        default:
                            obj = string.Empty;
                            break;
                    }
                    break;

                default:
                    obj = string.Empty;
                    break;
            }

            return obj;
        }
  
        /// <summary>
        /// 写 当前单元格值
        /// </summary>
        /// <param name="obj">值对象</param>
        public void WriteCell(object obj)
        {
            CheckNull(_currentCell, "WriteCell 失败！CurrentCell 为 Null");
            if (obj == null) return;

            var type = obj.GetType();
            if(type == typeof(string))
            {
                _currentCell.SetCellValue(obj.ToString());
            }else if(type == typeof(bool))
            {
                _currentCell.SetCellValue(Convert.ToBoolean(obj));
            }else if(type == typeof(DateTime))
            {
                _currentCell.SetCellValue(Convert.ToDateTime(obj));
            }
            else if (type == typeof(decimal) || type == typeof(double) || type == typeof(float)
                || type == typeof(int) || type == typeof(long) || type == typeof(short))
            {
                _currentCell.SetCellValue(Convert.ToDouble(obj));
            }else
            {
                _currentCell.SetCellValue(obj.ToString());
            }
            
        }

        #endregion

        #region 设置公式
        /// <summary>
        /// 设置单元格 公式
        /// </summary>
        /// <param name="formula">公式</param>
        public void WriteCellFormula(string formula)
        {

            CheckNull(_currentCell, "WriteCell 失败！CurrentCell 为 Null");
            if (string.IsNullOrEmpty(formula)) return;

            _currentCell.SetCellFormula(formula);
        }

        #endregion

        #region Save
        /// <summary>
        /// 保存文件
        /// </summary>
        /// <param name="file">文件路径</param>
        /// <param name="isReplace">是否替换</param>
        public void Save(string file, bool isReplace)
        {
            if(string.IsNullOrEmpty(file))
            {
                throw new Exception("保存文件失败！文件路径不能为空");
            }
            var extIndex = file.LastIndexOf(".");
            if(extIndex < 0)
            {
                throw new Exception("保存文件失败！文件路径无效：" + file);
            }
            if (File.Exists(file))
            {
                if(isReplace)
                {
                    File.Delete(file);
                }else
                {
                   
                    file = string.Format("{0}(1){1}", file.Substring(0, extIndex), file.Substring(extIndex));
                }
            }
            using (var fs = File.Create(file))
            {
                _workbook.Write(fs);
            }
        }

        public void Save(Stream stream)
        {
            _workbook.Write(stream);
            stream.Flush();
            stream.Position = 0;
        }

        public byte[] Save()
        {
            byte[] data = null;
            using (MemoryStream ms = new MemoryStream())
            {
                _workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                data = ms.GetBuffer();
            }
            return data;
        }
        #endregion
    }
}
